using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.IO;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Model;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid.Views.Grid.ViewInfo;
using DevExpress.XtraGrid.Views.Base;
using FirebirdSql.Data.FirebirdClient;

namespace FirebirdTool
{
    public partial class FbCsla : DevExpress.XtraEditors.XtraUserControl
    {
        public event FbEventHandler SavedEvent = null;
        private List<Column> lstColumn = null;
        private string tableName = null;
        private string realTableName = null;
        private int count = 0;
        //-----------------------------------------------------------

        #region form
        public FbCsla(string tableName, string realTableName)
        {
            InitializeComponent();

            this.tableName = tableName;
            this.realTableName = realTableName;

            this.gridView.ViewCaption = this.tableName;
            this.txtBO.Text = this.tableName + "BO";
            this.txtBOCollection.Text = this.tableName + "BOCollection";
            this.txtBOList.Text = this.tableName + "BOList";

            RefreshDB();

            this.KeyDown += new KeyEventHandler(FbCsla_KeyDown);
            this.timer1.Tick += new EventHandler(timer1_Tick);

            this.btnRefresh.Click += new EventHandler(btnRefresh_Click);
            this.btnCreate.Click += new EventHandler(btnCreate_Click);

            this.txtBO.TextChanged += new EventHandler(txtBO_TextChanged);
            this.txtBO.LostFocus += new EventHandler(txtBO_LostFocus);
            this.txtBOList.LostFocus += new EventHandler(txtBOList_LostFocus);

            this.chkBO.CheckedChanged += new EventHandler(chkBO_CheckedChanged);
            this.chkBOList.CheckedChanged += new EventHandler(chkBOList_CheckedChanged);

            chkBO_CheckedChanged(null, null);
            chkBOList_CheckedChanged(null, null);
        }

        private void FbCsla_KeyDown(object sender, KeyEventArgs e)
        {
        }
        #endregion form
        //-----------------------------------------------------------

        #region control
        private void timer1_Tick(object sender, EventArgs e)
        {
            this.pnInfo.Visible = false;
            this.timer1.Enabled = false;
            this.timer1.Stop();
        }

        private void chkBO_CheckedChanged(object sender, EventArgs e)
        {
            this.txtBO.Enabled = this.chkBO.Checked;
            this.txtBOCollection.Enabled = this.chkBO.Checked;
        }

        private void chkBOList_CheckedChanged(object sender, EventArgs e)
        {
            this.txtBOList.Enabled = this.chkBOList.Checked;
            this.txtCondition.Enabled = this.chkBOList.Checked;
            this.cmbKey.Enabled = this.chkBOList.Checked;
            this.cmbValue1.Enabled = this.chkBOList.Checked;
            this.cmbValue2.Enabled = this.chkBOList.Checked;
            this.cmbValue3.Enabled = this.chkBOList.Checked;
        }

        private void txtBO_TextChanged(object sender, EventArgs e)
        {
            if (this.txtBO.Text.EndsWith("BO"))
            {
                this.txtBOCollection.Text = this.txtBO.Text + "Collection";
                this.txtBOList.Text = this.txtBO.Text + "List";
            }
            else
            {
                this.txtBOCollection.Text = this.txtBO.Text + "BOCollection";
                this.txtBOList.Text = this.txtBO.Text + "BOList";
            }
        }

        private void txtBO_LostFocus(object sender, EventArgs e)
        {
            if (this.txtBO.Text.EndsWith("B"))
                this.txtBO.Text += "O";
            else
                if (this.txtBO.Text.EndsWith("BO") == false)
                    this.txtBO.Text += "BO";
        }

        private void txtBOList_LostFocus(object sender, EventArgs e)
        {
            if (this.txtBOList.Text.EndsWith("B"))
                this.txtBOList.Text += "OList";
            else
                if (this.txtBOList.Text.EndsWith("BO"))
                    this.txtBOList.Text += "List";
                else
                    if (this.txtBOList.Text.EndsWith("BOL"))
                        this.txtBOList.Text += "ist";
                    else
                        if (this.txtBOList.Text.EndsWith("BOLi"))
                            this.txtBOList.Text += "st";
                        else
                            if (this.txtBOList.Text.EndsWith("BOLis"))
                                this.txtBOList.Text += "t";
                            else
                                if (this.txtBOList.Text.EndsWith("BOList") == false)
                                    this.txtBOList.Text += "BOList";
        }
        #endregion control

        #region button
        private void btnRefresh_Click(object sender, EventArgs e)
        {
            RefreshDB();
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            Create();
        }
        #endregion button
        //-----------------------------------------------------------

        #region grid
        #endregion grid
        //-----------------------------------------------------------

        #region function
        private void RefreshDB()
        {
            this.lstColumn = Util.GetListColumn(realTableName);
            this.cmbKey.Properties.Items.Clear();
            this.cmbValue1.Properties.Items.Clear();
            this.cmbValue2.Properties.Items.Clear();
            this.cmbValue3.Properties.Items.Clear();

            this.cmbValue2.Properties.Items.Add("");
            this.cmbValue3.Properties.Items.Add("");

            this.cmbValue2.SelectedIndex = 0;
            this.cmbValue3.SelectedIndex = 0;

            for (int i = 0; i < this.lstColumn.Count; i++)
            {
                this.lstColumn[i].Deleted = true;
                this.cmbKey.Properties.Items.Add(this.lstColumn[i].Name);
                this.cmbValue1.Properties.Items.Add(this.lstColumn[i].Name);
                this.cmbValue2.Properties.Items.Add(this.lstColumn[i].Name);
                this.cmbValue3.Properties.Items.Add(this.lstColumn[i].Name);

                if (this.lstColumn[i].PK)
                    this.cmbKey.SelectedIndex = i;
            }

            this.bds.DataSource = this.lstColumn;
            this.gridView.RefreshData();
        }

        private void Create()
        {
            if (this.gridView.FocusedColumn.FieldName == "Deleted")
                this.gridView.FocusedColumn = this.colColumnName;
            else
                this.gridView.FocusedColumn = this.colDeleted;

            this.bds.EndEdit();
            this.lstColumn = (List<Column>)this.bds.DataSource;

            List<Column> lstCheckedColumn = new List<Column>();
            Column column = null;
            bool hasPK = false;
            Column clPK = null;

            int i = 0;
            int length = this.lstColumn.Count;
            for (i = 0; i < length; i++)
            {
                if (this.lstColumn[i].Deleted)
                {
                    Column newColumn = new Column();
                    newColumn = this.lstColumn[i].Clone();
                    lstCheckedColumn.Add(newColumn);

                    if (newColumn.PK)
                    {
                        clPK = newColumn.Clone();
                        hasPK = true;
                    }
                }
            }
            length = lstCheckedColumn.Count;


            #region validate
            this.dxErrorProvider1.SetError(this.txtBO, null);
            this.dxErrorProvider1.SetError(this.txtBOCollection, null);
            this.dxErrorProvider1.SetError(this.txtBOList, null);
            this.dxErrorProvider1.SetError(this.cmbKey, null);
            this.dxErrorProvider1.SetError(this.cmbValue1, null);

            if (this.chkBO.Checked)
            {
                if (hasPK == false)
                {
                    this.dxErrorProvider1.SetError(this.txtBO, "Select Primary Key");
                    return;
                }

                if (this.txtBO.Text == "")
                {
                    this.dxErrorProvider1.SetError(this.txtBO, "Enter BO's Name");
                    return;
                }

                if (length == 0)
                {
                    this.dxErrorProvider1.SetError(this.txtBO, "Select column");
                    return;
                }

                if (!this.chkGetBO.Checked && !this.chkInsert.Checked && !this.chkUpdate.Checked && !this.chkDelete.Checked)
                {
                    this.dxErrorProvider1.SetError(this.txtBO, "Check GetBO or Insert or Update or Delete");
                    return;
                }

                if (this.txtBO.Text.Length > 30)
                {
                    this.dxErrorProvider1.SetError(this.txtBO, "BO's name must not longer than 30 character");
                    return;
                }
            }

            if (this.chkBOList.Checked)
            {
                if (this.txtBOList.Text == "")
                {
                    this.dxErrorProvider1.SetError(this.txtBOList, "Enter BOList's Name");
                    return;
                }

                if (this.txtBOList.Text.Length > 32)
                {
                    this.dxErrorProvider1.SetError(this.txtBOList, "BOList's name must not longer than 32 character");
                    return;
                }

                if (this.cmbKey.Text == "")
                {
                    this.dxErrorProvider1.SetError(this.cmbKey, "Select Key column");
                    return;
                }

                if (this.cmbValue1.Text == "")
                {
                    this.dxErrorProvider1.SetError(this.cmbValue1, "Select Value column");
                    return;
                }
            }
            #endregion validate

            string BOName = this.txtBO.Text;
            string BOCollectionName = this.txtBOCollection.Text;
            string BOList = this.txtBOList.Text;
            string KeyName = this.cmbKey.Text;
            string ValueName1 = this.cmbValue1.Text;
            string ValueName2 = this.cmbValue2.Text;
            string ValueName3 = this.cmbValue3.Text;
            string Condition = this.txtCondition.Text;

            string Properties = "";
            string PKName = "";
            string RealPKName = "";
            string BOVarName = "";
            string SP_GetBOName = "";
            string SP_InsertBOName = "";
            string SP_UpdateBOName = "";
            string SP_GetBOCollectionName = "";
            string Fetch = "";
            string Insert = "";
            string Update = "";

            string KeyDataType = "";
            string KeyDataNullType = "";
            string ValueDataNullType = "";
            string OrderCols = "";
            string GetValueBOList = "";

            string scmd = "";
            string serror = null;
            string fileContent = null;
            string appPath = Application.StartupPath;
            if (appPath.EndsWith("\\") == false)
                appPath += "\\";

            #region BO & BOCollection
            if (this.chkBO.Checked)
            {
                #region GetBO
                SP_GetBOName = BOName + "_B";

                scmd = "RECREATE PROCEDURE " + SP_GetBOName + "(WHEREEXP VARCHAR(1000) CHARACTER SET UTF8)";
                scmd += "\r\nRETURNS (";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += "\r\n   " + column.RealName + " " + column.GetFireBirdDataType() + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);
                scmd += "\r\n)";

                scmd += "\r\nAS";
                scmd += "\r\nDECLARE VARIABLE STRSQL VARCHAR(8000) CHARACTER SET UTF8;";
                scmd += "\r\nBEGIN";
                scmd += "\r\n   STRSQL='SELECT FIRST 1";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += " " + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += " FROM " + this.realTableName + " WHERE ' || WHEREEXP;";

                scmd += "\r\n   FOR EXECUTE STATEMENT";
                scmd += "\r\n       STRSQL INTO";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += " :" + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += "\r\n   DO";
                scmd += "\r\n   BEGIN";
                scmd += "\r\n       SUSPEND;";
                scmd += "\r\n   END";
                scmd += "\r\nEND";

                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create GetBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //
                scmd = "COMMENT ON PROCEDURE " + SP_GetBOName + " IS '" + BOName + "_GetBO';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Desc GetBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion GetBO

                #region GetBOCollection
                SP_GetBOCollectionName = BOName + "_C";

                scmd = "RECREATE PROCEDURE " + SP_GetBOCollectionName + "(";
                scmd += "\r\n   WHEREEXP VARCHAR(1000) CHARACTER SET UTF8,";
                scmd += "\r\n   ORDERCOL VARCHAR(100) CHARACTER SET UTF8,";
                scmd += "\r\n   ORDEREXP VARCHAR(4) CHARACTER SET UTF8)";
                scmd += "\r\nRETURNS (";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += "\r\n   " + column.RealName + " " + column.GetFireBirdDataType() + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);
                scmd += "\r\n)";

                scmd += "\r\nAS";
                scmd += "\r\nDECLARE VARIABLE STRSQL VARCHAR(8000) CHARACTER SET UTF8;";
                scmd += "\r\nBEGIN";
                scmd += "\r\n   STRSQL='SELECT ";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += " " + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += " FROM " + this.realTableName + " ' || WHEREEXP || ' ORDER BY ' || ORDERCOL || ' ' || ORDEREXP;";

                scmd += "\r\n   FOR EXECUTE STATEMENT";
                scmd += "\r\n       STRSQL INTO";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += " :" + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += "\r\n   DO";
                scmd += "\r\n   BEGIN";
                scmd += "\r\n       SUSPEND;";
                scmd += "\r\n   END";
                scmd += "\r\nEND";

                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create GetBOCollection", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //
                scmd = "COMMENT ON PROCEDURE " + SP_GetBOCollectionName + " IS '" + BOName + "_GetBOCollection';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Desc GetBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion GetBOCollection

                #region Insert
                SP_InsertBOName = BOName + "_I";

                scmd = "RECREATE PROCEDURE " + SP_InsertBOName + "(";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    if (!column.PK)
                        scmd += "\r\n   " + column.RealName + " " + column.GetFireBirdDataType() + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);
                scmd += ")";

                scmd += "\r\nRETURNS (" + clPK.RealName + " " + clPK.GetFireBirdDataType() + ")";

                scmd += "\r\nAS";
                scmd += "\r\nBEGIN";
                scmd += "\r\n   INSERT INTO " + this.realTableName + "(";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += ")";
                scmd += "\r\n   VALUES (";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    if (column.PK)
                        scmd += "null,";
                    else
                        scmd += ":" + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += ")";
                scmd += "\r\n   RETURNING " + clPK.RealName + " INTO :" + clPK.RealName + ";";
                scmd += "\r\n   SUSPEND;";
                scmd += "\r\nEND";

                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create InsertBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //
                scmd = "COMMENT ON PROCEDURE " + SP_InsertBOName + " IS '" + BOName + "_Insert';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Desc InsertBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion Insert

                #region Update
                SP_UpdateBOName = BOName + "_U";

                scmd = "RECREATE PROCEDURE " + SP_UpdateBOName + "(";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    scmd += "\r\n   " + column.RealName + " " + column.GetFireBirdDataType() + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);
                scmd += ")";

                scmd += "\r\nAS";
                scmd += "\r\nBEGIN";
                scmd += "\r\n   UPDATE " + this.realTableName + " SET";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    if (!column.PK)
                        scmd += "\r\n       " + column.RealName + " = :" + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += "\r\n   WHERE " + clPK.RealName + " = :" + clPK.RealName + ";";
                scmd += "\r\nEND";

                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create UpdateBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //
                scmd = "COMMENT ON PROCEDURE " + SP_UpdateBOName + " IS '" + BOName + "_Update';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Desc UpdateBO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion Update

                //create BO
                //<%BOName%>
                //<%Properties%> : column list
                Properties = "";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    Properties += "        //" + column.RealName + "\r\n";
                    Properties += "        private " + column.CSharpNullDataType + " _" + column.Name + " = " + column.CSharpDefalutValue + ";\r\n";
                    Properties += "        public " + column.CSharpNullDataType + " " + column.Name + "\r\n";
                    Properties += "        {\r\n";
                    Properties += "            get { return this._" + column.Name + "; }\r\n";
                    Properties += "            set { this._" + column.Name + " = value; PropertyHasChanged(\"" + column.Name + "\"); }\r\n";
                    Properties += "        }\r\n\r\n";
                }

                //<%PKName%>
                PKName = clPK.Name;
                RealPKName = clPK.RealName;

                //<%BOVarName%>
                BOVarName = StaticUtil.GetVarName(BOName);

                //Fetch
                Fetch = "";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    Fetch += "            if (!(dataReader[\"" + column.RealName + "\"] is DBNull))\r\n";
                    if (column.CSharpDataType == "bool")
                        Fetch += "                this._" + column.Name + " = (((string)dataReader[\"" + column.RealName + "\"]).Trim() == \"1\");\r\n";
                    else
                        if (column.CSharpDataType == "string")
                            Fetch += "                this._" + column.Name + " = ((string)dataReader[\"" + column.RealName + "\"]).Trim();\r\n";
                        else
                            if (column.CSharpDataType == "double")
                                Fetch += "                this._" + column.Name + " = Convert.ToDouble(dataReader[\"" + column.RealName + "\"]);\r\n";
                            else
                                Fetch += "                this._" + column.Name + " = (" + column.CSharpDataType + ")dataReader[\"" + column.RealName + "\"];\r\n";
                }

                //Insert & Update
                Insert = "";
                Update = "";
                for (i = 0; i < length; i++)
                {
                    column = lstCheckedColumn[i];
                    if (column.CSharpDataType == "bool")
                    {
                        if (column.PK == false)
                            Insert += "                    cmd.Parameters.Add(\"@" + column.RealName + "\", FbDbType.Char).Value = (this._" + column.Name + ".Value ? \"1\" : \"0\");\r\n";

                        Update += "                    cmd.Parameters.Add(\"@" + column.RealName + "\", FbDbType.Char).Value = (this._" + column.Name + ".Value ? \"1\" : \"0\");\r\n";
                    }
                    else
                    {
                        if (column.PK == false)
                            Insert += "                    cmd.Parameters.Add(\"@" + column.RealName + "\", FbDbType." + column.FbDataType + ").Value = this._" + column.Name + ";\r\n";

                        Update += "                    cmd.Parameters.Add(\"@" + column.RealName + "\", FbDbType." + column.FbDataType + ").Value = this._" + column.Name + ";\r\n";
                    }
                }

                #region Create file
                fileContent = File.ReadAllText(appPath + "SP_BO.txt");
                fileContent = fileContent.Replace("<%Namespace%>", Util.Namespace);
                fileContent = fileContent.Replace("<%BOName%>", BOName);
                fileContent = fileContent.Replace("<%Properties%>", Properties);
                fileContent = fileContent.Replace("<%PKName%>", PKName);
                fileContent = fileContent.Replace("<%SP_GetBOName%>", SP_GetBOName);
                fileContent = fileContent.Replace("<%RealPKName%>", RealPKName);
                fileContent = fileContent.Replace("<%Fetch%>", Fetch);
                fileContent = fileContent.Replace("<%SP_InsertBOName%>", SP_InsertBOName);
                fileContent = fileContent.Replace("<%Insert%>", Insert);
                fileContent = fileContent.Replace("<%SP_UpdateBOName%>", SP_UpdateBOName);
                fileContent = fileContent.Replace("<%Update%>", Update);
                fileContent = fileContent.Replace("<%RealTableName%>", this.realTableName);
                if (Directory.Exists(Util.ProjectPath + this.tableName + "\\") == false)
                    Directory.CreateDirectory(Util.ProjectPath + this.tableName + "\\");
                File.WriteAllText(Util.ProjectPath + this.tableName + "\\" + BOName + ".cs", fileContent, Encoding.UTF8);

                fileContent = File.ReadAllText(appPath + "SP_BOCollection.txt");
                fileContent = fileContent.Replace("<%Namespace%>", Util.Namespace);
                fileContent = fileContent.Replace("<%BOName%>", BOName);
                fileContent = fileContent.Replace("<%RealPKName%>", RealPKName);
                fileContent = fileContent.Replace("<%SP_GetBOCollectionName%>", SP_GetBOCollectionName);
                fileContent = fileContent.Replace("<%BOVarName%>", BOVarName);
                fileContent = fileContent.Replace("<%PKName%>", PKName);
                if (Directory.Exists(Util.ProjectPath + this.tableName + "\\") == false)
                    Directory.CreateDirectory(Util.ProjectPath + this.tableName + "\\");
                File.WriteAllText(Util.ProjectPath + this.tableName + "\\" + BOCollectionName + ".cs", fileContent, Encoding.UTF8);
                #endregion Create file
            }
            #endregion BO & BOCollection

            #region BOList
            if (this.chkBOList.Checked)
            {
                Column keyColumn = GetColumnByName(this.cmbKey.Text);

                OrderCols = "";

                #region get list value column
                List<Column> lstColumnBOList = new List<Column>();
                if (string.IsNullOrEmpty(this.cmbValue1.Text) == false)
                {
                    Column newColumnBOList1 = GetColumnByName(this.cmbValue1.Text);
                    if (newColumnBOList1 != null)
                    {
                        OrderCols += (OrderCols == "" ? "" : ", ") + newColumnBOList1.RealName;
                        lstColumnBOList.Add(newColumnBOList1);
                    }
                }

                if (string.IsNullOrEmpty(this.cmbValue2.Text) == false)
                {
                    Column newColumnBOList2 = GetColumnByName(this.cmbValue2.Text);
                    if (newColumnBOList2 != null)
                    {
                        OrderCols += (OrderCols == "" ? "" : ", ") + newColumnBOList2.RealName;
                        lstColumnBOList.Add(newColumnBOList2);
                    }
                }

                if (string.IsNullOrEmpty(this.cmbValue3.Text) == false)
                {
                    Column newColumnBOList3 = GetColumnByName(this.cmbValue3.Text);
                    if (newColumnBOList3 != null)
                    {
                        OrderCols += (OrderCols == "" ? "" : ", ") + newColumnBOList3.RealName;
                        lstColumnBOList.Add(newColumnBOList3);
                    }
                }
                #endregion get list value column

                #region BOList
                length = lstColumnBOList.Count;

                scmd = "RECREATE PROCEDURE " + BOList + "(";
                scmd += "\r\n   WHEREEXP VARCHAR(1000) CHARACTER SET UTF8,";
                scmd += "\r\n   ORDERCOL VARCHAR(100) CHARACTER SET UTF8,";
                scmd += "\r\n   ORDEREXP VARCHAR(4) CHARACTER SET UTF8)";

                scmd += "\r\nRETURNS (";
                scmd += "\r\n   " + keyColumn.RealName + " " + keyColumn.GetFireBirdDataType() + ",";
                for (i = 0; i < length; i++)
                {
                    column = lstColumnBOList[i];
                    scmd += "\r\n   " + column.RealName + " " + column.GetFireBirdDataType() + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);
                scmd += "\r\n)";

                scmd += "\r\nAS";
                scmd += "\r\nDECLARE VARIABLE STRSQL VARCHAR(8000) CHARACTER SET UTF8;";
                scmd += "\r\nBEGIN";

                scmd += "\r\n   STRSQL='SELECT " + keyColumn.RealName + ",";
                for (i = 0; i < length; i++)
                {
                    column = lstColumnBOList[i];
                    scmd += " " + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += " FROM " + this.realTableName + " ' || WHEREEXP || ' ORDER BY ' || ORDERCOL || ' ' || ORDEREXP;";

                scmd += "\r\n   FOR EXECUTE STATEMENT";
                scmd += "\r\n       STRSQL INTO :" + keyColumn.RealName + ",";
                for (i = 0; i < length; i++)
                {
                    column = lstColumnBOList[i];
                    scmd += " :" + column.RealName + ",";
                }
                if (scmd.EndsWith(","))
                    scmd = scmd.Remove(scmd.Length - 1);

                scmd += "\r\n   DO";
                scmd += "\r\n   BEGIN";
                scmd += "\r\n       SUSPEND;";
                scmd += "\r\n   END";
                scmd += "\r\nEND";

                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create BOList", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //
                scmd = "COMMENT ON PROCEDURE " + BOList + " IS '" + BOList + "';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Desc BOList", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion BOList

                KeyDataType = keyColumn.CSharpDataType;
                KeyDataNullType = keyColumn.CSharpNullDataType;

                GetValueBOList = "";
                if (lstColumnBOList.Count == 1)
                {
                    ValueDataNullType = lstColumnBOList[0].CSharpNullDataType;
                    GetValueBOList += "                            Value = null;\r\n";
                    GetValueBOList += "                            if (!(reader[\"" + lstColumnBOList[0].RealName + "\"] is DBNull))\r\n";
                    GetValueBOList += "                                Value += (string)reader[\"" + lstColumnBOList[0].RealName + "\"];\r\n";
                }
                else
                {
                    ValueDataNullType = "string";
                    GetValueBOList += "                            Value = \"\";\r\n";

                    for (i = 0; i < length; i++)
                    {
                        column = lstColumnBOList[i];
                        GetValueBOList += "                            if (!(reader[\"" + lstColumnBOList[0].RealName + "\"] is DBNull))\r\n";
                        GetValueBOList += "                                Value += (Value == \"\" ? \"\" : \" \") + reader[\"" + lstColumnBOList[0].RealName + "\"].ToString();\r\n";
                    }
                }

                #region Create file
                fileContent = File.ReadAllText(appPath + "SP_BOList.txt");
                fileContent = fileContent.Replace("<%Namespace%>", Util.Namespace);
                fileContent = fileContent.Replace("<%BOList%>", BOList);
                fileContent = fileContent.Replace("<%KeyDataNullType%>", KeyDataNullType);
                fileContent = fileContent.Replace("<%ValueDataNullType%>", ValueDataNullType);
                fileContent = fileContent.Replace("<%OrderCols%>", OrderCols);
                fileContent = fileContent.Replace("<%ValueDataNullType%>", ValueDataNullType);
                fileContent = fileContent.Replace("<%GetValueBOList%>", GetValueBOList);
                fileContent = fileContent.Replace("<%KeyDataType%>", KeyDataType);
                fileContent = fileContent.Replace("<%KeyName%>", KeyName);

                if (Directory.Exists(Util.ProjectPath + "BOlist\\") == false)
                    Directory.CreateDirectory(Util.ProjectPath + "BOlist\\");
                File.WriteAllText(Util.ProjectPath + "BOlist\\" + BOList + ".cs", fileContent, Encoding.UTF8);
                #endregion Create file
            }
            #endregion BOList

            this.count++;
            this.lblInfo.Text = "Create " + this.count;
            this.pnInfo.Visible = true;
            this.Update();

            this.timer1.Enabled = true;
            this.timer1.Start();

            if (this.SavedEvent != null)
            {
                //FbEventArgs evt = new FbEventArgs(this.lstColumn, this.tableName);
                //this.SavedEvent(this, evt);
                this.SavedEvent(this, null);
            }
        }

        private Column GetColumnByName(string columnName)
        {
            int i = 0;
            int length = this.lstColumn.Count;
            for (i = 0; i < length; i++)
                if (this.lstColumn[i].Name == columnName)
                    return this.lstColumn[i].Clone();

            return null;
        }
        #endregion function
    }
}
